<?php

require_once(dirname(__FILE__).'/../../../WildPack/WildPack.php');
$wp = new WildPack();
$wp->using('util');
$wp->using('dataaccess.wpconnect');

class TestWpConnectSqlite extends WpTestSuite
{
  private $_path = '';
  private $_table = 'ctest';
  
  /**
   * Preparation
   */
  public function prepare()
  {   
    $this->_path = dirname(__FILE__).'/ctest.db';
    $this->addMessage('Using the following db file: '.$this->_path);
    
    $connection = new Sqlite3($this->_path);
    if (!$connection) {
      $this->addMessage('Tyring to connect to db ... failed.');
      return false;
    }
    $this->addMessage('Tyring to connect to db ... established.');
    
    $strSql = 'SELECT count(type) FROM sqlite_master WHERE type = \'table\' AND name = \'ctest\'';
    $result = $connection->query($strSql);
    if($result === false){
      $this->addMessage('System error determining the table existence');
      return false;
    }
    
    $createtab = true;
    while ($row = $result->fetchArray()) {
     if ($row[0] > 0) {
       $createtab = false;
       break;
     }
    }
    
    if ($createtab) {
      if (!$connection->query('CREATE TABLE  '.$this->_table.' ('.
        'field1 varchar(255) NOT NULL,'.
        'field2 int NOT NULL,'.
        'field3 float NULL,'.
        'field4 text NULL,'.
        'field5 bool NULL'.
        ')'
      )) {
        $this->addMessage('Table not found. Try to create ... failed. Test stopped.');
        return false;
      } else {
        $this->addMessage('Table not found. Try to create ... successfull.');
      }
    } else {
      $this->addMessage('Table found.');
    }
    
    if (!$connection->query('DELETE FROM '.$this->_table)) {
      $this->addMessage('Trying to empty the table ... failed. Test stopped.');
      return false;
    }
    
    $this->addMessage('Trying to empty the table ... successfull.');
    
    return true;
  }
  
  /**
   * Test
   */
  public function test()
  {
    /* Single requests */
    $l = new WpMemLog();
    
    $c = new WpConnectSqlite3(array(
      'location' => $this->_path,
      'log' => $l
    ));
    $this->addMessage('Single requests');
    
    //-- Insert --
    $this->addMessage('Insert tests');
    $chk = $c->insert($this->_table, array('non_ex_field' => 'blubb', 'field2' => 'bla'));
    $this->addResult('Insert with wrong field', false, $chk, '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (1)', 'WpConnectSqlite3.php/WpConnectSqlite3::insert', $lo['location'], '===');
    $this->addResult('Check log (2)', 'Error in SQL query', $lo['title'], '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (3)', null, $lo, 'null');
    
    $chk = $c->insert('non_existing_table', array('non_ex_field' => 'blubb', 'field2' => 'bla'));
    $this->addResult('Insert to not existing table', false, $chk, '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (1)', 'WpConnectSqlite3.php/WpConnectSqlite3::insert', $lo['location'], '===');
    $this->addResult('Check log (2)', 'Error in SQL query', $lo['title'], '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (3)', null, $lo, 'null');
    
    $chk = $c->insert($this->_table, array('field1' => 'blubb', 'field3' => 1.2));
    $this->addResult('Forget mand. field', false, $chk, '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (1)', 'WpConnectSqlite3.php/WpConnectSqlite3::insert', $lo['location'], '===');
    $this->addResult('Check log (2)', 'Error in SQL query', $lo['title'], '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (3)', null, $lo, 'null');
    
    $chk = $c->insert($this->_table, array('field1' => 'blubb', 'field2' => 2, 'field3' => 1.2));
    $this->addResult('Insert standard', true, $chk, '===');
    
    
    
    //-- Select --
    $this->addMessage('Select tests');
    $chk = $c->select('', $this->_table, '');
    $this->addResult('Select all', true, $chk, '===');
    $as = $c->getResult('assocs');
    $ob = $c->getResult('objects');
    $ar = $c->getResult('arrays');
    $this->addResult('Return value (1)', null, $as, 'array');
    $this->addResult('Return value (2)', null, $ob, 'array');
    $this->addResult('Return value (3)', null, $ar, 'array');
    $this->addResult('Return value (4)', 1, $as, 'count');
    $this->addResult('Return value (5)', 1, $ob, 'count');
    $this->addResult('Return value (6)', 1, $ar, 'count');
    $this->addResult('Return value (7)', 'blubb', $as[0]['field1'], '===');
    $this->addResult('Return value (8)', 'blubb', $ob[0]->field1, '===');
    $this->addResult('Return value (9)', 'blubb', $ar[0][0], '===');
    $this->addResult('Return value (7)', 1.2, $as[0]['field3'], '===');
    $this->addResult('Return value (8)', 1.2, $ob[0]->field3, '===');
    $this->addResult('Return value (9)', 1.2, $ar[0][2], '===');
    
    //-- Update --
    $this->addMessage('Update tests');
    $chk = $c->update($this->_table, array('field1' => 'blubb', 'field2' => 2,'field3' => 1.2));
    $this->addResult('Update without condition', true, $chk, '===');
    $chk = $c->select('', $this->_table, '');
    $this->addResult('Select all', true, $chk, '===');
    $as = $c->getResult('assocs');
    $this->addResult('Return value (1)', null, $as, 'array');
    $this->addResult('Return value (2)', 1, $as, 'count');
    $this->addResult('Return value (3)', 'blubb', $as[0]['field1'], '===');
    $this->addResult('Return value (4)', 2, $as[0]['field2'], '===');
    $this->addResult('Return value (5)', 1.2, $as[0]['field3'], '===');
    
    $chk = $c->update($this->_table, array('field1' => 'blubb2', 'field2' => 3,'field3' => 2.2), 'WHERE field1 = \'blubb\'');
    $this->addResult('Update with condition', true, $chk, '===');
    $chk = $c->select('', $this->_table, '');
    $this->addResult('Select all', true, $chk, '===');
    $as = $c->getResult('assocs');
    $this->addResult('Return value (1)', null, $as, 'array');
    $this->addResult('Return value (2)', 1, $as, 'count');
    $this->addResult('Return value (3)', 'blubb2', $as[0]['field1'], '===');
    $this->addResult('Return value (4)', 3, $as[0]['field2'], '===');
    $this->addResult('Return value (5)', 2.2, $as[0]['field3'], '===');
    
    $chk = $c->update('non_existing_table', array('field1' => 'blubb', 'field2' => 2,'field3' => 1.2));
    $this->addResult('Update with non-existing table', false, $chk, '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (1)', 'WpConnectSqlite3.php/WpConnectSqlite3::update', $lo['location'], '===');
    $this->addResult('Check log (2)', 'Error in SQL query', $lo['title'], '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (3)', null, $lo, 'null');
    
    $chk = $c->update($this->_table, array());
    $this->addResult('Update with empty field array', false, $chk, '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (1)', 'WpConnectSqlite3.php/WpConnectSqlite3::update', $lo['location'], '===');
    $this->addResult('Check log (2)', 'Error in SQL query', $lo['title'], '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (3)', null, $lo, 'null');
    
    $chk = $c->update($this->_table, null);
    $this->addResult('Update with null field array', false, $chk, '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (1)', 'WpConnectSqlite3.php/WpConnectSqlite3::update', $lo['location'], '===');
    $this->addResult('Check log (2)', 'Method failed', $lo['title'], '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (3)', null, $lo, 'null');
    
    $this->addMessage('Delete tests');
    $chk = $c->delete($this->_table, 'WHERE 1=2');
    $this->addResult('Delete with false condition', true, $chk, '===');
    $chk = $c->select('', $this->_table, '');
    $this->addResult('Select all', true, $chk, '===');
    $as = $c->getResult('assocs');
    $this->addResult('Return value (1)', null, $as, 'array');
    $this->addResult('Return value (2)', 1, $as, 'count');
    
    $chk = $c->delete('non_existing_table', '');
    $this->addResult('Delete with non-existing table', false, $chk, '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (1)', 'WpConnectSqlite3.php/WpConnectSqlite3::delete', $lo['location'], '===');
    $this->addResult('Check log (2)', 'Error in SQL query', $lo['title'], '===');
    $lo = $l->grabNewest();
    $this->addResult('Check log (3)', null, $lo, 'null');
    
    $chk = $c->delete($this->_table, '');
    $this->addResult('Delete with no condition', true, $chk, '===');
    $chk = $c->select('', $this->_table, '');
    $this->addResult('Select all', true, $chk, '===');
    $as = $c->getResult('assocs');
    $this->addResult('Return value (1)', null, $as, 'array');
    $this->addResult('Return value (2)', 0, $as, 'count');
    
    //-- Multi request --
    $this->addMessage('Multi requests');
    
    $this->addMessage('Insert (only)');
    $c->startMultiRequest(true, false, false);
    $chk = $c->insert($this->_table, array('field1' => 'blubb1', 'field2' => 2, 'field3' => 1.2, 'field4' => 'text', 'field5' => 1));
    $this->addResult('Insert (MR)', true, $chk, '===');
    $chk = $c->insert($this->_table, array('field1' => 'blubb2', 'field2' => 3, 'field3' => 1.2, 'field4' => 'text', 'field5' => 0));
    $this->addResult('Insert (MR)', true, $chk, '===');
    $chk = $c->insert($this->_table, array('field1' => 'blubb3', 'field2' => 4, 'field3' => 1.2, 'field4' => 'text', 'field5' => 1));
    $this->addResult('Insert (MR)', true, $chk, '===');
    $chk = $c->select('', $this->_table, '');
    $this->addResult('Select all', true, $chk, '===');
    $as = $c->getResult('assocs');
    $this->addResult('Return value (1)', null, $as, 'array');
    $this->addResult('Return value (2)', 0, $as, 'count');
    $chk = $c->delete($this->_table, '');
    $this->addResult('Delete all (no MR)', true, $chk, '===');
    $this->addResult('Check # of MR', 3, $c->countMultiRequests(), '===');
    $chk = $c->stopMultiRequest(true);
    $this->addResult('Process (MR)', 3, $chk, '===');
    
    $chk = $c->select('', $this->_table, '');
    $this->addResult('Select all', true, $chk, '===');
    $as = $c->getResult('assocs');
    $this->addResult('Return value (1)', null, $as, 'array');
    $this->addResult('Return value (2)', 3, $as, 'count');
    $this->addResult('Return value (3)', 'blubb1', $as[0]['field1'], '===');
    $this->addResult('Return value (4)', 2, $as[0]['field2'], '===');
    $this->addResult('Return value (5)', 1.2, $as[0]['field3'], '===');
    $this->addResult('Return value (6)', 'text', $as[0]['field4'], '===');
    $this->addResult('Return value (7)', 1, $as[0]['field5'], '===');
    $this->addResult('Return value (10)', 'blubb2', $as[1]['field1'], '===');
    $this->addResult('Return value (11)', 3, $as[1]['field2'], '===');
    $this->addResult('Return value (12)', 1.2, $as[1]['field3'], '===');
    $this->addResult('Return value (13)', 'text', $as[1]['field4'], '===');
    $this->addResult('Return value (14)', 0, $as[1]['field5'], '===');
    
    $this->addMessage('Insert, update, delete');
    $c->startMultiRequest(true, true, true);
    $chk = $c->insert($this->_table, array('field1' => 'blubb4', 'field2' => 5, 'field3' => 1.2, 'field4' => 'text', 'field5' => 1));
    $this->addResult('Insert (MR)', true, $chk, '===');
    $chk = $c->delete($this->_table, 'WHERE field2 = 3');
    $this->addResult('Delete (MR)', true, $chk, '===');
    $chk = $c->update($this->_table, array('field1' => 'blubb5', 'field2' => 6), 'WHERE field1 = \'blubb3\'');
    $this->addResult('Update (MR)', true, $chk, '===');
    $chk = $c->select('', $this->_table, '');
    $this->addResult('Select all', true, $chk, '===');
    $as = $c->getResult('assocs');
    $this->addResult('Return value (1)', null, $as, 'array');
    $this->addResult('Return value (2)', 3, $as, 'count');
    $chk = $c->stopMultiRequest(true);
    $this->addResult('Process (MR)', 3, $chk, '===');
    
    $chk = $c->select('', $this->_table, '', 0, 0, 'field2');
    $this->addResult('Select all', true, $chk, '===');
    $as = $c->getResult('assocs');
    $this->addResult('Return value (1)', null, $as, 'array');
    $this->addResult('Return value (2)', 3, $as, 'count');
    $this->addResult('Return value (3)', 'blubb1', $as[0]['field1'], '===');
    $this->addResult('Return value (4)', 2, $as[0]['field2'], '===');
    $this->addResult('Return value (5)', 1.2, $as[0]['field3'], '===');
    $this->addResult('Return value (6)', 'text', $as[0]['field4'], '===');
    $this->addResult('Return value (7)', 1, $as[0]['field5'], '===');
    $this->addResult('Return value (10)', 'blubb4', $as[1]['field1'], '===');
    $this->addResult('Return value (11)', 5, $as[1]['field2'], '===');
    $this->addResult('Return value (12)', 1.2, $as[1]['field3'], '===');
    $this->addResult('Return value (13)', 'text', $as[1]['field4'], '===');
    $this->addResult('Return value (14)', 1, $as[1]['field5'], '===');
    $this->addResult('Return value (10)', 'blubb5', $as[2]['field1'], '===');
    $this->addResult('Return value (11)', 6, $as[2]['field2'], '===');
    $this->addResult('Return value (12)', 1.2, $as[2]['field3'], '===');
    $this->addResult('Return value (13)', 'text', $as[2]['field4'], '===');
    $this->addResult('Return value (14)', 1, $as[2]['field5'], '===');
  }
  
  /**
   * Cleanup
   */
  public function cleanup()
  {
    return;
    if (file_exists($this->path)) {
      try {
        unlink($this->path);
      } catch(Exception $e) {
        $this->addMessage('Warning: An error occurred, deleting the old dbfile ('.$this->path.').');
      }
      $this->addMessage('Old db file was successfully deleted.');
    }
  } 
}

$t = new TestWpConnectSqlite();
$t->start('WpConnectSqlite');
